package com.spring.boot.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
   
/**   
* POI操作Excel   
* @since: 1.0   
* @version:1.0   
* @author 
*/   
public class PoiExcelTool {    
    
/** 
 * 复制单个文件 
 * @param oldPath String 原文件路径 如：c:/fqf.txt 
 * @param newPath String 复制后路径 如：f:/fqf.txt 
 * @return boolean 
 */ 
public static boolean copyFile(String oldPath, String newPath) { 
   try { 
       int bytesum = 0; 
       int byteread = 0; 
       File oldfile = new File(oldPath); 
       if (oldfile.exists()) { //文件存在时
           InputStream inStream = new FileInputStream(oldPath); //读入原文件 
           FileOutputStream fs = new FileOutputStream(newPath); 
           byte[] buffer = new byte[1444]; 
           int length; 
           while ( (byteread = inStream.read(buffer)) != -1) { 
               bytesum += byteread; //字节数 文件大小 
//               System.out.println(bytesum); 
               fs.write(buffer, 0, byteread); 
           } 
           inStream.close(); 
           fs.close();
       } 
       return true;
   } 
   catch (FileNotFoundException e) { 
       System.out.println("复制单个文件操作出错"); 
       e.printStackTrace(); 
       return false;
   } catch (Exception e) {
	   e.printStackTrace(); 
	   return false;
   }
} 

/**   
 * 根据源Sheet样式copy新Sheet   
 * @param fromsheetname   
 * @param newsheetname   一个sheet中名称不能相同
 * @param targetFile   
 */   
public static void copySheet(String fromsheetname, String newsheetname,String targetFile) {    
    HSSFWorkbook wb = null;    
    HSSFSheet newsheet = null;
    try {    
        FileInputStream fis = new FileInputStream(targetFile);    
        wb = new HSSFWorkbook(fis);    
        HSSFSheet fromsheet = wb.getSheet(fromsheetname);    
        if (fromsheet != null && wb.getSheet(newsheetname) == null) {    
            newsheet = wb.createSheet(newsheetname);    
            //设置打印参数    
            newsheet.setMargin(Sheet.TopMargin,fromsheet.getMargin(Sheet.TopMargin));// 页边距（上）    
            newsheet.setMargin(Sheet.BottomMargin,fromsheet.getMargin(Sheet.BottomMargin));// 页边距（下）    
            newsheet.setMargin(Sheet.LeftMargin,fromsheet.getMargin(Sheet.LeftMargin) );// 页边距（左）    
            newsheet.setMargin(Sheet.RightMargin,fromsheet.getMargin(Sheet.RightMargin));// 页边距（右    
                
            HSSFPrintSetup ps = newsheet.getPrintSetup();    
            ps.setLandscape(false); // 打印方向，true：横向，false：纵向(默认)    
            ps.setVResolution((short)600);    
            ps.setPaperSize(PrintSetup.A4_PAPERSIZE); //纸张类型    
                
            File file = new File(targetFile);    
            if (file.exists() && (file.renameTo(file))) {    
                copyRows(wb, fromsheet, newsheet, fromsheet.getFirstRowNum(), fromsheet.getLastRowNum());    
                FileOutputStream fileOut = new FileOutputStream(targetFile);    
                wb.write(fileOut);    
                fileOut.flush();    
                fileOut.close();    
            } else {    
                System.out.println("文件不存在或者正在使用,请确认…");    
            }    
            fis.close();
        }    
        fis.close();    
    } catch (Exception e) {    
        e.printStackTrace();    
    }  finally {
        //return newsheet;
    }  
}    
    
/**   
 * 拷贝Excel行   
 * @param wb   
 * @param fromsheet   
 * @param newsheet   
 * @param firstrow   
 * @param lastrow   
 */   
public static void copyRows(HSSFWorkbook wb, HSSFSheet fromsheet,HSSFSheet newsheet, int firstrow, int lastrow) {    
    if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) {    
        return;    
    }    
    // 拷贝合并的单元格    
    Region region = null;    
    for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {    
        region = fromsheet.getMergedRegionAt(i);    
        if ((region.getRowFrom() >= firstrow)&& (region.getRowTo() <= lastrow)) {    
            newsheet.addMergedRegion(region);    
        }    
    }    

    HSSFRow fromRow = null;    
    HSSFRow newRow = null;    
    HSSFCell newCell = null;    
    HSSFCell fromCell = null;    
    // 设置列宽    
    for (int i = firstrow; i <= lastrow; i++) {    
        fromRow = fromsheet.getRow(i);    
        if (fromRow != null) {    
            for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {    
                int colnum = fromsheet.getColumnWidth((short) j);    
                if (colnum > 100) {    
                    newsheet.setColumnWidth((short) j, (short) colnum);    
                }    
                if (colnum == 0) {    
                    newsheet.setColumnHidden((short) j, true);    
                } else {    
                    newsheet.setColumnHidden((short) j, false);    
                }    
            }    
            break;    
        }    
    }    
    // 拷贝行并填充数据    
    for (int i = 0; i <= lastrow; i++) {    
        fromRow = fromsheet.getRow(i);    
        if (fromRow == null) {    
            continue;    
        }    
        newRow = newsheet.createRow(i - firstrow);
        newRow.setHeight(fromRow.getHeight());    
        for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {    
            fromCell = fromRow.getCell((short) j);    
            if (fromCell == null) {    
                continue;    
            }    
            newCell = newRow.createCell((short) j);    
            newCell.setCellStyle(fromCell.getCellStyle());    
            int cType = fromCell.getCellType();    
            newCell.setCellType(cType);    
            switch (cType) {    
            case Cell.CELL_TYPE_STRING:    
                newCell.setCellValue(fromCell.getRichStringCellValue());    
                break;    
            case Cell.CELL_TYPE_NUMERIC:    
                newCell.setCellValue(fromCell.getNumericCellValue());    
                break;    
            case Cell.CELL_TYPE_FORMULA:    
                newCell.setCellFormula(fromCell.getCellFormula());    
                break;    
            case Cell.CELL_TYPE_BOOLEAN:    
                newCell.setCellValue(fromCell.getBooleanCellValue());    
                break;    
            case Cell.CELL_TYPE_ERROR:    
                newCell.setCellValue(fromCell.getErrorCellValue());    
                break;    
            default:    
                newCell.setCellValue(fromCell.getRichStringCellValue());    
                break;    
            }    
        }    
    }    
}

/**
 * 设置单元格样式
 * @param wb
 * @param fontName 字体名称
 * @param fontHeight 字体大小
 * @param borderLeft 是否有左边框
 * @param borderRight 是否有右边框
 * @param borderTop 是否有上边框
 * @param borderBottom 是否有下边框
 * @param boldweightBold 是否加粗
 * @param alignLeft 是否左对齐
 * @param alignRight 是否右对齐
 * @param alignCenter 是否居中对齐
 * @param valignTop 是否居上对齐
 * @param valignCenter 是否垂直居中对齐
 * @param valignBottom 是否居下对象
 * @param
 * @param
 * @return
 */
public static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook wb,String fontName,int fontHeight,
		boolean borderLeft,boolean borderRight,boolean borderTop,boolean borderBottom,
		boolean boldweightBold,boolean alignLeft,boolean alignRight,boolean alignCenter,
		boolean valignTop,boolean valignCenter,boolean valignBottom){
	HSSFCellStyle   cs   =   wb.createCellStyle();
	if(borderLeft) cs.setBorderLeft(CellStyle.BORDER_THIN);
	if(borderRight) cs.setBorderRight(CellStyle.BORDER_THIN);
	if(borderTop) cs.setBorderTop(CellStyle.BORDER_THIN);
	if(borderBottom) cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setWrapText(true);//自动换行
    if(alignLeft) cs.setAlignment(CellStyle.ALIGN_LEFT);//左对齐
    if(alignRight) cs.setAlignment(CellStyle.ALIGN_RIGHT);//右对齐
    if(alignCenter) cs.setAlignment(CellStyle.ALIGN_CENTER);//居中对齐
    if(valignTop) cs.setVerticalAlignment(CellStyle.VERTICAL_TOP);//居上对齐
    if(valignCenter) cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中对齐
    if(valignBottom) cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);//居下对齐
    HSSFFont font = wb.createFont();
    if(StringUtils.isNotBlank(fontName)) font.setFontName(fontName);
    if(boldweightBold) font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗
    font.setFontHeight((short)fontHeight);
    font.setCharSet(Font.DEFAULT_CHARSET);
    cs.setFont(font);
    return cs;
}

/**
 * 设置单元格样式
 * @param wb
 * @param fontName 字体名称
 * @param fontHeightInPoints 字体大小
 * @param borderLeft 是否有左边框
 * @param borderRight 是否有右边框
 * @param borderTop 是否有上边框
 * @param borderBottom 是否有下边框
 * @param boldweightBold 是否加粗
 * @param alignLeft 是否左对齐
 * @param alignRight 是否右对齐
 * @param alignCenter 是否居中对齐
 * @param valignTop 是否居上对齐
 * @param valignCenter 是否垂直居中对齐
 * @param valignBottom 是否居下对象
 * @param
 * @param
 * @return
 */
public static HSSFCellStyle getHSSFCellStyle1(HSSFWorkbook wb,String fontName,short fontHeightInPoints,
		boolean borderLeft,boolean borderRight,boolean borderTop,boolean borderBottom,
		boolean boldweightBold,boolean alignLeft,boolean alignRight,boolean alignCenter,
		boolean valignTop,boolean valignCenter,boolean valignBottom){
	HSSFCellStyle   cs   =   wb.createCellStyle();
	if(borderLeft) cs.setBorderLeft(CellStyle.BORDER_THIN);
	if(borderRight) cs.setBorderRight(CellStyle.BORDER_THIN);
	if(borderTop) cs.setBorderTop(CellStyle.BORDER_THIN);
	if(borderBottom) cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setWrapText(true);//自动换行
    if(alignLeft) cs.setAlignment(CellStyle.ALIGN_LEFT);//左对齐
    if(alignRight) cs.setAlignment(CellStyle.ALIGN_RIGHT);//右对齐
    if(alignCenter) cs.setAlignment(CellStyle.ALIGN_CENTER);//居中对齐
    if(valignTop) cs.setVerticalAlignment(CellStyle.VERTICAL_TOP);//居上对齐
    if(valignCenter) cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中对齐
    if(valignBottom) cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);//居下对齐
    HSSFFont font = wb.createFont();
    if(StringUtils.isNotBlank(fontName)) font.setFontName(fontName);
    if(boldweightBold) font.setBoldweight(Font.BOLDWEIGHT_BOLD);//加粗
    font.setFontHeightInPoints(fontHeightInPoints);
    font.setCharSet(Font.DEFAULT_CHARSET);
    cs.setFont(font);
    return cs;
}

/**
 * 删除临时文件方法
 * @param file
 * @return
 */
public static boolean delFile(File file){
    if (!file.exists()) {
    System.out.println("文件不存在");
    return false;
    }
    boolean rs = file.delete();
    if (rs){
//        System.out.println("文件删除成功");
        return true;
    }
    else{
//        System.out.println("文件删除失败");
        return false;
    }
}

public static void main(String[] args) {    
    
    String filePath = "D:/downloadtest/20110915184913288686.xls";
    File file = new File(filePath);
    delFile(file);
    
    /** copy    sheet 方法 */
//    FileInputStream fis = null;
//    try {
//        PoiExcelSheetCopy ew = new PoiExcelSheetCopy();    
//        fis = new FileInputStream("d:/template4.xls");    
//        HSSFWorkbook wb = new HSSFWorkbook(fis);    
//        for(int i = 0; i < 5; i++){
//            HSSFSheet newSheet = ew.copySheet("template", "test3"+i, "d:/template4.xls");
//            String sheetName = newSheet.getSheetName();
//            System.out.println(sheetName);
//        }
//    } catch (Exception e) {
//        e.printStackTrace();
//    }finally{
//        try {
//            if(fis != null){
//                fis.close();
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        
//    }
    
    /** copy    文件 方法 */
//    String aa = "d:/exptest.xls";
//    String bb = "d:/exptest1.xls";
//    boolean cc = PoiExcelSheetCopy.copyFile(aa, bb);
//    System.out.println(cc);
    
}    
}    
